﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace QLInternet.Model
{
    public class Query
    {

        public String sql = "select KHACHHANG.MaKhachHang,HoTen,CMND,NgheNghiep,DiaChi,DienThoai,Email,DiaChiCaiDat,DiaChiHoaDon,SoLuongTaiKhoan,TenNguoiSuDung,DANGKY.TronGoi,TenKieuCaiDat,TenMoDem,TenGoiCuoc from KHACHHANG" +
            "  inner join DANGKY on KHACHHANG.MaKhachHang = DANGKY.MaKhachhang " +
            " inner join GOICUOC on DANGKY.MaGoiCuoc = GOICUOC.MaGoiCuoc " +
            " inner join CHITIETKIEUCAIDAT  on DANGKY.MaChiTietKieuCaiDat = CHITIETKIEUCAIDAT.MaChiTiet " +
            " inner join KIEUCAIDAT on KIEUCAIDAT.MaKieuCaiDat =  CHITIETKIEUCAIDAT.MaKieuCaiDat " +
            "inner join MODEM on CHITIETKIEUCAIDAT.MaMoDem = MODEM.MaMoDem where tinhtrangthanhtoan = 0";

        public String sqlKh = "select KHACHHANG.MaKhachHang,HoTen,CMND,NgheNghiep,DiaChi,DienThoai,Email,DiaChiCaiDat,DiaChiHoaDon,SoLuongTaiKhoan,TenNguoiSuDung,DANGKY.TronGoi,TenKieuCaiDat,TenMoDem,TenGoiCuoc,TenTinhTrang,MaHopDong,LoiKyThuat from KHACHHANG" +
            "  inner join DANGKY on KHACHHANG.MaKhachHang = DANGKY.MaKhachhang " +
            " inner join GOICUOC on DANGKY.MaGoiCuoc = GOICUOC.MaGoiCuoc " +
            " inner join CHITIETKIEUCAIDAT  on DANGKY.MaChiTietKieuCaiDat = CHITIETKIEUCAIDAT.MaChiTiet " +
            " inner join KIEUCAIDAT on KIEUCAIDAT.MaKieuCaiDat =  CHITIETKIEUCAIDAT.MaKieuCaiDat " +
            "inner join MODEM on CHITIETKIEUCAIDAT.MaMoDem = MODEM.MaMoDem " +
            " inner join hopdong on khachhang.makhachhang = hopdong.makhachhang "+
            " inner join tinhtrangsudung on  hopdong.matinhtrang = tinhtrangsudung.matinhtrangsudung  ";

        public String sqlDay = "select KHACHHANG.MaKhachHang,HoTen,CMND,NgheNghiep,DiaChi,DienThoai,Email,DiaChiCaiDat,DiaChiHoaDon,SoLuongTaiKhoan,TenNguoiSuDung,DANGKY.TronGoi,TenKieuCaiDat,TenMoDem,TenGoiCuoc from KHACHHANG" +
           "  inner join DANGKY on KHACHHANG.MaKhachHang = DANGKY.MaKhachhang " +
           " inner join GOICUOC on DANGKY.MaGoiCuoc = GOICUOC.MaGoiCuoc " +
           " inner join CHITIETKIEUCAIDAT  on DANGKY.MaChiTietKieuCaiDat = CHITIETKIEUCAIDAT.MaChiTiet " +
           " inner join KIEUCAIDAT on KIEUCAIDAT.MaKieuCaiDat =  CHITIETKIEUCAIDAT.MaKieuCaiDat " +
           "inner join MODEM on CHITIETKIEUCAIDAT.MaMoDem = MODEM.MaMoDem where Date_Format(NgayDangKy,'%d/%m/%Y')  = '";
        public String sqlThanhToan = "select KHACHHANG.MaKhachHang,HoTen,CMND,DienThoai,DiaChi,dongia,NgayDangKy from khachhang " +
            " inner join dangky on KHACHHANG.MaKhachHang = DANGKY.MaKhachhang " +
            " inner join chitietkieucaidat on DANGKY.MaChiTietKieuCaiDat = CHITIETKIEUCAIDAT.MaChiTiet where Tinhtrangthanhtoan = 0 and DANGKY.MaChiTietKieuCaiDat <> 0";
        public String sqlGoiCuoc = "select MaGoiCuoc,TenGoiCuoc from GOICUOC where magoicuoc <> 0";
        public String sqlKieuCaiDat = "select MaKieuCaiDat, TenKieuCaiDat from KIEUCAIDAT where MaKieuCaiDat <> 0";
        public String sqlGetMaPhuongThuc = "select MaPhuongThuc from GOICUOC where MaGoiCuoc = ";
        public String queryModem = "Select * from MODEM where mamodem <> 0";
        public String sqlMaModem = "Select MaModem from CHITIETKIEUCAIDAT where MaKieuCaiDat =";
        public String sqlModem = "Select * from MODEM where MaModem =";

        public String checkTenKh = "select CMND from KHACHHANG where HoTen = '";
        public String checkCmnd = "select Hoten from KHACHHANG where CMND = '";

        public String sqlGetMaKh = "select makhachhang from khachhang where cmnd = '";

        public String checkDienThoai = "select hoten from KHACHHANG where DienThoai = '";
        public String checkDiachi = "select hoten from KHACHHANG where DiaChi = '";
        public String checkEmail = "select hoten from KHACHHANG where Email = '";
        public String checkNgheNghiep = "select hoten from KHACHHANG where nghenghiep = '";

        public String checkDccd = "select makhachhang from DangKy where DiaChiCaiDat = '";
        public String checkDchd = "select makhachhang from DangKy where DiaChiHoaDon = '";
        public String checkSltk = "select makhachhang from DangKy where SoLuongTaiKhoan = '";
        public String checktenNguoiSuDung = "select makhachhang from DangKy where TenNguoiSuDung = '";
        public String checkMactkcd = "select makhachhang from DangKy where MaChiTietKieuCaiDat = '";
        public String checkPhuongThuc = "select makhachhang from DangKy where trongoi = '";
        public String checkTinhTrangSuDung = "select makhachhang from hopdong where matinhtrang = '";

        public String timKiemKh = "select KHACHHANG.MaKhachHang,HoTen,CMND,DienThoai,DiaChi,dongia,NgayDangKy from khachhang " +
            " inner join dangky on KHACHHANG.MaKhachHang = DANGKY.MaKhachhang " +
            " inner join chitietkieucaidat on DANGKY.MaChiTietKieuCaiDat = CHITIETKIEUCAIDAT.MaChiTiet  where KHACHHANG.MaKhachHang = DANGKY.MaKhachhang ";
        public String sqlTinhTrangSuDung = "select * from tinhtrangsudung where matinhtrangsudung <> 0";
        public String sqlTimKiemKhachHang = "select Khachhang.makhachhang,hoten,cmnd,dienthoai,diachi,TenGoiCuoc,DANGKY.TronGoi,TenTinhTrang,MaHopDong,LoiKyThuat from khachhang " +
            " inner join dangky on KHACHHANG.MaKhachHang = DANGKY.MaKhachhang " +
            " inner join goicuoc on DANGKY.MaGoiCuoc = GOICUOC.MaGoiCuoc " +
            " inner join hopdong on khachhang.makhachhang = hopdong.makhachhang " +
            " inner join tinhtrangsudung on  hopdong.matinhtrang = tinhtrangsudung.matinhtrangsudung where LoiKyThuat = 0 ";
        public String thongTinLoi = "select ngaybaoloi,thongtinloi,ngaysualoi,cachkhacphuc from baoloi where Mabaoloi = ";
        public String thongTinThanhToan = "select tungay,denngay,tongtien,tinhtrang from thanhtoan where makhachhang = ";
        public String sqlUsers = "select id from dangnhap where taikhoan like '";
        public String sqlPassWord = "select id from dangnhap where taikhoan like '";
        public String getMaBaoLoi = "select mabaoloi from baoloi inner join hopdong on baoloi.mahopdong = hopdong.mahopdong " +
                   " where baoloi.mahopdong = ";
        public String sqlmctcd = "select MaChiTiet from CHITIETKIEUCAIDAT where MaKieuCaiDat = '";
        public String sqlTtThanhToan = "select tinhtrangthanhtoan from dangky where makhachhang = ";

        public String sqlDoiMatKhau = "update dangnhap set matkhau = '";
    }
}
